import sqlite3
import os


class Database:
    def __init__(self, db_name):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()
        self.create_table()

    def create_table(self):
        # 创建表
        self.cursor.execute(
            '''CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE
            )'''
        )
        print("表创建成功")

    def insert_user(self, name, email, replace=False):
        # 插入数据
        if replace:
            self.cursor.execute('''
                INSERT OR REPLACE INTO users (name, email) VALUES (?, ?)
            ''', (name, email))
        else:
            self.cursor.execute('''
                INSERT OR REPLACE INTO users (name, email) VALUES (?, ?)
            ''', (name, email))

    def find_all_users(self):
        # 查询数据
        self.cursor.execute('SELECT * FROM users')
        return self.cursor.fetchall()

    def commit(self):
        # 提交事务
        self.conn.commit()

    def close(self):
        # 关闭游标和连接
        self.cursor.close()
        self.conn.close()


def check_path():
    # 查看当前工作目录
    print("当前工作目录:", os.getcwd())


def main():
    check_path()
    db = Database('mydatabase.db')

    # 插入数据
    db.insert_user('Alice', 'alice@example.com')
    db.insert_user('Bob', 'bob@example.com')
    db.insert_user('Alice', 'alice@example.com')  # 尝试插入重复的电子邮件
    db.insert_user('Alice', 'alice_new@example.com', replace=True)  # 替换现有的 Alice 记录

    # 提交事务
    db.commit()

    # 查询并打印所有用户
    users = db.find_all_users()
    for index, user in enumerate(users):
        print(index, user)

    # 关闭数据库连接
    db.close()


if __name__ == "__main__":
    main()

